# @Project: diablo
# @File: level_insert_agreement_price
# @Time: 2023/7/12 17:57
# @User: Jolly
# @Email: jolly@007vin.com
"""
# code is far away from bugs with the god animal protecting
    I love animals. They taste delicious.
              ┏┓      ┏┓
            ┏┛┻━━━┛┻┓
            ┃      ☃      ┃
            ┃  ┳┛  ┗┳  ┃
            ┃      ┻      ┃
            ┗━┓      ┏━┛
                ┃      ┗━━━┓
                ┃  神兽保佑    ┣┓
                ┃ 永无BUG！   ┏┛
                ┗┓┓┏━┳┓┏┛
                  ┃┫┫  ┃┫┫
                  ┗┻┛  ┗┻┛ 
"""

# 小汽配2.0按照一二级品牌写入定价协议


import datetime
import time
import threading
from queue import Queue

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure


quality_dict = {
    "level1": {
        "0,50": 40,
        "51,100": 35,
        "101,150": 30,
        "151,200": 25,
        "201,300": 20,
        "301,500": 15,
        "501,800": 12,
        "801,1000": 10,
        "1001,1300": 8,
        "1301,1500": 7,
        "1501,1800": 6,
        "1801,2000": 5.5,
        "2001,2200": 5,
        "2201,2500": 4.5,
        "2501,2800": 4,
        "2801,3100": 3.5,
        "3101,3500": 3,
        "3501,5000": 2.5
    },
    "level2": {
        "0,50": 100,
        "51,100": 65,
        "101,150": 60,
        "151,200": 50,
        "201,300": 45,
        "301,400": 40,
        "401,600": 35,
        "601,800": 30,
        "801,1000": 25,
        "1001,1200": 20,
        "1201,1300": 15,
        "1301,1500": 10,
        "1501,1800": 8,
        "1801,2200": 7,
        "2201,2400": 6.5,
        "2401,2600": 6,
        "2601,2800": 5.5,
        "2801,3000": 5,
        "3001,3200": 4.5,
        "3201,3500": 4,
        "3501,3700": 3.5,
        "3701,3900": 3
    },
    "baoxiao": {
        "0,50": 60,
        "51,100": 50,
        "101,150": 35,
        "151,200": 30,
        "201,300": 25,
        "301,500": 20,
        "501,800": 15,
        "801,1000": 13,
        "1001,1300": 12,
        "1301,1500": 10,
        "1501,1800": 8,
        "1801,2000": 7,
        "2001,2200": 6.5,
        "2201,2500": 6,
        "2501,2800": 5.5,
        "2801,3100": 5,
        "3101,3500": 4.5,
        "3501,5000": 4
    }
}


class InitAgreementPrice:

    def __init__(self, level, agreement_excel_id):

        if level == "level1":
            self.file_path = "./hongde_quality_level1.xlsx"
        elif level == "level2":
            self.file_path = "./hongde_quality_level2.xlsx"
        elif level == "baoxiao":
            self.file_path = "./hongde_quality_level_baoxiao.xlsx"

        self.level = level
        self.agreement_excel_id = agreement_excel_id

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=50,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def format_agreement_price(self, agreement_price):
        """
        协议价格修改时处理
        浮点数保留两位小数，不四舍五入
        """
        agreement_price = str(agreement_price)

        if '.' in agreement_price:
            integer_part, decimal_part = agreement_price.split('.')
            decimal_part = decimal_part[:2]
            agreement_price = float(f'{integer_part}.{decimal_part}')
        else:
            agreement_price = int(agreement_price)

        return agreement_price

    # 通过描述查询库存
    def get_stock_by_quality(self, quality_desc):

        stocks_identify = []

        quality_desc_info = quality_desc.split("=")

        if len(quality_desc_info) > 0:

            ori_name = quality_desc_info[0]
            now_name = quality_desc_info[1]

            connection = self.diablo_conn_2_0

            try:
                if connection is not None:
                    # 执行数据库操作
                    db = connection["inventory_data"]
                    conn = db["ChangYuan_meta_stocks"]

                    filter_dict = {
                        # '$or': [
                        #     {'ori_quality': ori_name},
                        #     {'quality': now_name},
                        # ]
                        'ori_quality': ori_name
                    }
                    docs = conn.find(filter_dict, {'identify': 1, 'quality': 1, 'sale_price': 1})

                    for doc in docs:
                        identify_dict = {'identify': doc.get("identify", ""),
                                         'quality': doc.get("quality", ""),
                                         'sale_price': doc.get("sale_price", "")}

                        stocks_identify.append(identify_dict)

            except Exception as e:
                print("获取用户库存数据identify失败 - mongo：", e)
            finally:
                # 将连接放回连接池
                connection.close()

        # 没查找到数据的记录至文本中
        if len(stocks_identify) == 0:
            with open("./search_fail_level1.txt", "a") as file:
                file.write(quality_desc + "\n")

            return False, False

        previous = stocks_identify[0]
        for row in stocks_identify:
            yield previous, False
            previous = row
        yield previous, True

    # 判断定价协议表是否已有对应数据
    def check_agreement_exist(self, identify, agreement_excel_id):

        check = False

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_excel"]

                filter_dict = {
                    "identify": identify,
                    "agreement_excel_id": agreement_excel_id
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 1:
                    check = True

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check

    # 写入数据
    def write_data(self, *args):

        identify = args[0]

        # 返回的库存identify，再去查询agreement_price中，是否存在数据，存在则跳过
        exist = self.check_agreement_exist(identify.get("identify"), self.agreement_excel_id)

        # 存在则跳过
        if exist:
            return

        # 不存在，按照规则写入
        percent = 0
        price_rules = quality_dict.get(self.level)
        for rules, price in price_rules.items():
            min_price = rules.split(",")[0]
            max_price = rules.split(",")[1]
            if int(min_price) <= identify.get("sale_price") <= int(max_price):
                percent = price
                break

        # 百分比
        percent_value = float(percent / 100)

        # 最终金额
        agreement_price = identify.get("sale_price") * round((1 + percent_value), 2)

        # 金额处理
        agreement_price = self.format_agreement_price(agreement_price)

        agreement_dict = {
            "agreement_excel_id": self.agreement_excel_id,
            "identify": identify.get("identify"),
            "agreement_price": agreement_price,  # 最终计算金额
            "price_type": "percent_add",
            "price_value": percent_value,  # 增加百分比数额
            "price_desc": f'¥ = {identify.get("sale_price")}*{round((1 + percent_value) * 100, 2)}%',
            "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        }

        # 获取2.0MongoDB连接
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_excel"]
                conn.insert_one(dict(agreement_dict))

        except Exception as e:
            print("写入2.0MongoDB数据库单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 获取excel数据，生成器返回
    def get_excel_data(self):

        with open(self.file_path, "rb") as file:
            content = file.read()

        # 将内容转换为BytesIO对象
        bytesIO = BytesIO(content)

        # 加载工作簿
        book = load_workbook(bytesIO, read_only=True)

        # 获取活动工作表
        sheet = book.active

        # previous = next(sheet.iter_rows(), None)
        # for row in sheet.iter_rows():
        #     yield previous, False
        #     previous = row
        # yield previous, True
        return sheet.iter_rows()

    # 任务执行
    def task(self):
        q = Queue(maxsize=200)  # 设定最大队列数和线程数

        count = 1

        for row in self.get_excel_data():
            # 定价描述
            quality_desc = row[0].value  # "TAUD=拓德TAUD"

            # 根据品质查询库存信息
            for row, last in self.get_stock_by_quality(quality_desc):

                if row is False:
                    continue

                t = threading.Thread(target=self.write_data, args=(row, ))
                q.put(t)
                if (q.full() == True) or last:
                    thread_list = []
                    while q.empty() == False:
                        t = q.get()
                        thread_list.append(t)
                        t.start()
                    for t in thread_list:
                        t.join()

                print("当前写入数：", count, " 品质为：", quality_desc)

                if last:
                    break

                count += 1


if __name__ == "__main__":
    InitAgreementPrice("level1", 51)



